package oa.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import oa.dbcon.DBConnection;
import oa.lyk.service.Personoffice.Page;
import oa.pojo.Dept;
import oa.pojo.Salary;
import oa.view.ContactView;
import oa.view.SalaryView;

public class SalaryDao {
	public SalaryView search(String id){
		SalaryView view = null;
		String sql="select v.*,row_number() over " +
		"(order by to_number(sal_id)) rid from (select sal.*,fil.user_name " +
		"from salary sal inner join files fil on sal.user_id=fil.user_id) " +
		"v where sal_status=1 and sal_id=?";
		Connection con = DBConnection.getDBConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql);
			pst.setString(1, id);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				view = new SalaryView();
				view.setSalId(rs.getString(1));
				view.setUserId(rs.getString(2));
				view.setDeptName(rs.getString(3));
				view.setStuffJob(rs.getString(4));
				view.setBaseSalary(rs.getString(5));
				view.setBonus(rs.getString(6));
				view.setSalFine(rs.getString(7));
				view.setSalStatus(rs.getString(8));
				view.setUserName(rs.getString(9));
			}
			pst.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBConnection.closeDBConnection(con);
		return view;
	}
	public Page query(int currentPage,String name) {
		Page page = new Page();
		page.setCurrentPage(currentPage);

		List<SalaryView> viewList = new ArrayList<SalaryView>();
		StringBuffer sql;
		if(name ==null || "".equals(name)){
			sql = new StringBuffer("select v.*,row_number() over " +
					"(order by to_number(sal_id)) rid from (select sal.*,fil.user_name " +
					"from salary sal inner join files fil on sal.user_id=fil.user_id) " +
					"v where sal_status=1");
		}else{
			sql = new StringBuffer(
					"select v.*,row_number() over " +
					"(order by to_number(sal_id)) rid from (select sal.*,fil.user_name " +
					"from salary sal inner join files fil on sal.user_id=fil.user_id) " +
					"v where sal_status=1 and user_name=");
			sql.append("'").append(name).append("'");
		}
		StringBuffer pageSql = new StringBuffer(sql);
		pageSql.insert(0, "select * from (");
		pageSql.append(") where rid>=").append(
				(currentPage - 1) * Page.PAGE_SIZE + 1).append(" and rid<=")
				.append((currentPage - 1) * Page.PAGE_SIZE + Page.PAGE_SIZE);

		Connection con = DBConnection.getDBConnection();
		try {
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(pageSql.toString());
			while (rs.next()) {
				SalaryView view = new SalaryView();
				view.setSalId(rs.getString(1));
				view.setUserId(rs.getString(2));
				view.setDeptName(rs.getString(3));
				view.setStuffJob(rs.getString(4));
				view.setBaseSalary(rs.getString(5));
				view.setBonus(rs.getString(6));
				view.setSalFine(rs.getString(7));
				view.setSalStatus(rs.getString(8));
				view.setUserName(rs.getString(9));
				viewList.add(view);
			}
			rs.close();
			st.close();
			page.setSalaryData(viewList);

			StringBuffer totalSql = new StringBuffer(sql);
			totalSql.insert(0, "select count(*) from (");
			totalSql.append(")");
			st = con.createStatement();

			int lineNum = 0;
			rs = st.executeQuery(totalSql.toString());
			if (rs.next()) {
				lineNum = rs.getInt(1);
			}
			int totalPage = (lineNum + Page.PAGE_SIZE - 1) / Page.PAGE_SIZE;
			rs.close();
			st.close();
			page.setTotalPage(totalPage);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		DBConnection.closeDBConnection(con);

		return page;
	}
	
	public boolean updateById(Salary sal){
		boolean flag = false;
		String sql="update salary set base_salary=?,bonus=?,sal_fine=? where sal_id=?";
		Connection con = DBConnection.getDBConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql);
			pst.setString(1, sal.getBaseSalary());
			pst.setString(2, sal.getBonus());
			pst.setString(3, sal.getSalFine());
			pst.setString(4, sal.getSalId());
			if(pst.executeUpdate()>0){
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public boolean del(String userid){
		boolean flag = false;
		String sql="update salary set sal_status=0 where user_id=?";
		Connection con = DBConnection.getDBConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql);
			pst.setString(1, userid);
			if(pst.executeUpdate()>0){
				flag=true;
			}
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBConnection.closeDBConnection(con);
		return flag;
	}
	
	public boolean saveSalary(String userid,String dept,String work,String base){
		boolean flag = false;
		String sql="insert into salary values(seq_salary.nextVal,?,?,?,?,'0','0','1')";
		Connection con = DBConnection.getDBConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql);
			pst.setString(1, userid);
			pst.setString(2, dept);
			pst.setString(3, work);
			pst.setString(4, base);
			if(pst.executeUpdate()>0){
				flag=true;
			}
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBConnection.closeDBConnection(con);
		return flag;
	}
	
}
